SQL Property Example

This example demonstrates the SQL property by setting and changing the SQL property of a temporary QueryDef and comparing the results. The SQLOutput function is required for this procedure to run.

Sub SQLX()

   Dim dbsNorthwind As Database
   Dim qdfTemp As QueryDef
   Dim rstEmployees As Recordset

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set qdfTemp = dbsNorthwind.CreateQueryDef("")

   ' Open Recordset using temporary QueryDef object and 
   ' print report.
   SQLOutput "SELECT * FROM Employees " & _
      "WHERE Country = 'USA' " & _
      "ORDER BY LastName", qdfTemp

   ' Open Recordset using temporary QueryDef object and 
   ' print report.
   SQLOutput "SELECT * FROM Employees " & _
      "WHERE Country = 'UK' " & _
      "ORDER BY LastName", qdfTemp

   dbsNorthwind.Close

End Sub

Function SQLOutput(strSQL As String, qdfTemp As QueryDef)

   Dim rstEmployees As Recordset

   ' Set SQL property of temporary QueryDef object and open 
   ' a Recordset.
   qdfTemp.SQL = strSQL
   Set rstEmployees = qdfTemp.OpenRecordset

   Debug.Print strSQL

   With rstEmployees
      ' Enumerate Recordset.
      Do While Not .EOF
         Debug.Print "  " & !FirstName & " " & _
            !LastName & ", " & !Country
         .MoveNext
      Loop
      .Close
   End With

End Function